package com.one.rope.mvp.web.common.util.excel;

import com.one.rope.mvp.basecore.util.ArrayUtils;
import com.one.rope.mvp.basecore.util.ClassUtils;
import com.one.rope.mvp.basecore.util.FieldUtils;
import com.one.rope.mvp.basecore.util.StringUtils;
import com.one.rope.mvp.basecore.exception.BusinessDataException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 读取Excel工具类
 */
public class ExcelReaderUtil {

  protected static org.apache.log4j.Logger logger = org.apache.log4j.Logger
      .getLogger(ExcelReaderUtil.class);

  private static ExcelReaderUtil excelReader;

  private ExcelReaderUtil() {
  }

  public static ExcelReaderUtil instance() {
    if (excelReader == null) {
      synchronized (ExcelReaderUtil.class) {
        if (excelReader == null) {
          excelReader = new ExcelReaderUtil();
        }
      }
    }
    return excelReader;
  }

  public void saveToExcel(List<List<Object>> lists, String sheetName, List<String> headerNames,
      File file) throws Exception {
    HSSFWorkbook workbook = null;
    try {
      //ExcelReaderUtil.class.getResource("template.xlsx");
      //		File templateFile = ResourceUtils.getFile("classpath:template.xlsx");
      //	workbook = WorkbookFactory.create(templateFile);
      workbook = new HSSFWorkbook();

      //Sheet sheet = workbook.getSheetAt(0);
      HSSFSheet sheet = workbook.createSheet(sheetName);

      int startIndex = 0;
      Row row = null;
      if (!ArrayUtils.isEmpty(headerNames)) {
        row = sheet.createRow(0);
        ++startIndex;
        for (int j = 0; j < headerNames.size(); ++j) {
          Cell cell = row.createCell(j);
          cell.setCellValue(headerNames.get(j));
        }
      }

      for (int i = 0; i < lists.size(); ++i) {
        List<Object> list = lists.get(i);
        row = sheet.createRow(i + startIndex);
        for (int j = 0; j < list.size(); ++j) {
          Cell cell = row.createCell(j);
          String value = FieldUtils.typeToString(list.get(j).getClass(), list.get(j));
          cell.setCellValue(value);
        }
      }

      try (FileOutputStream fos = new FileOutputStream(file.getPath())) {
        workbook.write(fos);
      }
    } catch (Exception e) {
      throw e;
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (Exception e2) {
          throw e2;
        }
      }
    }
  }

  /**
   * 读取Excel 流文件
   *
   * @param is inputStream
   * @param cls model
   * @param offsetLine 第几行开始
   * @param limitLine 最大多少行
   */
  public <T> List<T> readToListObject(InputStream is, Class<T> cls, int offsetLine, int limitLine,
      int sheetIndex) throws Exception {
    return readToListObject(is, cls, offsetLine, limitLine, sheetIndex, null, null);
  }

  public <T> List<T> readToListObject(InputStream is, Class<T> cls, int offsetLine, int limitLine,
      int sheetIndex, ExcelExceptionFunction<T> statement) throws Exception {
    return readToListObject(is, cls, offsetLine, limitLine, sheetIndex, statement, null);
  }

  public <T> List<T> readToListObject(InputStream is, Class<T> cls, int offsetLine, int limitLine,
      int sheetIndex
      , ExcelExceptionFunction<T> statement
      , ExcelBoolFunction<List<String>> headStatement)
      throws Exception {
    List<T> results = new ArrayList<>(100);

    List<Field> fields = FieldUtils.getFields(cls); //cls.getDeclaredFields();
    /**
     * 遍历获取最大的索引和注解
     */
    Map<Field, ExcelHeader> excelHeaderMap = new HashMap<>(10);
    int defaultMaxIndex = 0, realMaxIndex = 0;
    for (Field field : fields) {
      ExcelHeader excelHeader = field.getAnnotation(ExcelHeader.class);
      if (excelHeader != null) {
        excelHeaderMap.put(field, excelHeader);
        if (excelHeader.maxIndex() > defaultMaxIndex) {
          defaultMaxIndex = excelHeader.maxIndex();
        }
        if (excelHeader.index() < 0) {
          realMaxIndex = -1;
        }
      }
    }

    /**
     * 读取Excel数据
     */
    int maxColumnIndex = realMaxIndex < 0 ? defaultMaxIndex : realMaxIndex;
    Map<Field, Integer> fieldToIndex = new HashMap<>(excelHeaderMap.size());
    List<List<String>> lines = readRowsToList(is, offsetLine, limitLine, sheetIndex, maxColumnIndex,
        (line, index) -> {
          findFieldIndex(excelHeaderMap, fieldToIndex, line);
          if (index == 0 && headStatement != null) {
            headStatement.execute(line, 0);
          }
          return true;
        });

    Map<Field, Method> exefieldToIndex = fieldToIndex.keySet().stream()
        .collect(Collectors.toMap(item -> item, item -> {
          try {
            return cls.getMethod("set" + StringUtils.upperLeftOne(item.getName()), item.getType());
          } catch (Exception e) {
            return null;
          } finally {
          }
        }));
    /**
     * 如果从0行开始读,忽略表头
     */
    int startIndex = offsetLine == 0 ? 1 : 0;
    int realRow = offsetLine;
    for (int i = startIndex; i < lines.size(); ++i, ++realRow) {
      List<String> lineList = lines.get(i);
      T t = ClassUtils.instance(cls);
      for (Field field : fieldToIndex.keySet()) {
        ExcelHeader excelHeader = field.getAnnotation(ExcelHeader.class);
        int columnIndex = fieldToIndex.get(field);
        try {
          Method exeMethod = exefieldToIndex.get(field);
          String cellValue = lineList.get(columnIndex);
          if (excelHeader.autoTrim()) {
            cellValue = StringUtils.trim(cellValue);
          }
          Object value = null;
          if (excelHeader.convert() == DefaultConvert.class) {
            value = FieldUtils.excelCastString(field.getType(), cellValue);
          } else {
            ExcelConvert<T> convert = ClassUtils.instance(excelHeader.convert());
            value = convert.convert(t, cellValue);
          }
          exeMethod.invoke(t, value);
        } catch (Exception e) {
          if (statement == null) {
            throw e;
          } else {
            statement.execute(t, excelHeader, realRow, columnIndex, lineList.get(columnIndex));
          }
        }

      }
      results.add(t);
    }

    return results;
  }

  private void findFieldIndex(Map<Field, ExcelHeader> excelHeaderMap,
      Map<Field, Integer> fieldToIndex,
      List<String> line) {
    for (Field field : excelHeaderMap.keySet()) {
      ExcelHeader excelHeader = excelHeaderMap.get(field);
      if (excelHeader.index() >= 0) {
        fieldToIndex.put(field, excelHeader.index());
      } else {
        boolean isFound = false;
        for (int i = 0; i < line.size(); ++i) {
          String lineItem = line.get(i);
          if (lineItem.equalsIgnoreCase(excelHeader.title())) {
            isFound = true;
            fieldToIndex.put(field, i);
            break;
          } else if (!StringUtils.isNullOrEmpty(excelHeader.contain())) {
            if (StringUtils.contains(lineItem, excelHeader.contain(), true)) {
              isFound = true;
              fieldToIndex.put(field, i);
              break;
            }
          }
        }
        if (!isFound) {
          throw new BusinessDataException("没有找到excel的标题[" + excelHeader.title() + "]");
        }
      }
    }
  }

  public List<List<String>> readRowsToList(InputStream is, int offsetLine, int limitLine,
      int sheetIndex,
      int maxColumnIndex) throws Exception {
    return readRowsToList(is, offsetLine, limitLine, sheetIndex, maxColumnIndex, null);
  }

  /**
   * 读取Excel文件流到List列表中
   */
  public List<List<String>> readRowsToList(InputStream is, int offsetLine, int limitLine,
      int sheetIndex,
      int maxColumnIndex, ExcelBoolFunction<List<String>> headStatement) throws Exception {
    Workbook workbook = null;
    try {
      workbook = WorkbookFactory.create(is);
      Sheet sheet = workbook.getSheetAt(sheetIndex);
      List<List<String>> lines = readRowsToList(workbook, offsetLine, limitLine, sheet,
          maxColumnIndex);
      if (headStatement != null) {
        List<String> headLine = null;
        if (offsetLine > 0) {
          headLine = readRow(workbook, sheet, 0, maxColumnIndex);
        } else {
          headLine = lines.get(0);
        }
        headStatement.execute(headLine, 0);
      }
      return lines;
    } catch (Exception e) {
      throw e;
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (Exception e2) {
          throw e2;
        }
      }
    }
  }

  private List<List<String>> readRowsToList(Workbook workbook, int offsetLine, int limitLine,
      Sheet sheet,
      int maxColumnIndex) throws Exception {
    List<List<String>> list = new ArrayList<>();
    long maxLine =
        sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : (sheet.getLastRowNum() - offsetLine + 1);

    for (int i = offsetLine; i <= maxLine; i++) {
      List<String> rows = readRow(workbook, sheet, i, maxColumnIndex);
      if (rows == null || rows.size() == 0) {
        continue;
      }

      list.add(rows);
    }
    return list;
  }

  private List<String> readRow(Workbook workbook, Sheet sheet, int rowIndex, int maxColumnIndex) {
    Row row = sheet.getRow(rowIndex);
    List<String> rows = new ArrayList<>(maxColumnIndex + 1);
    if (null == row) {
      return null;
    } else {
      char startChar = 'A';
      for (int c = 0; c <= maxColumnIndex; ++c) {
        Cell cell = row.getCell(CellReference.convertColStringToIndex(String.valueOf(startChar)));
        rows.add(getCellValue(cell));
        startChar += 1;
      }
    }
    return rows;
  }

  private static String getCellValue(Cell c) {
    if (c == null) {
      return "";
    }
    String o;
    switch (c.getCellTypeEnum()) {
      case BLANK:
        o = "";
        break;
      case BOOLEAN:
        o = String.valueOf(c.getBooleanCellValue());
        break;
      case FORMULA:
        o = calculationFormula(c);
        break;
      case NUMERIC:
        o = String.valueOf(c.getNumericCellValue());
        o = matchDoneBigDecimal(o);
        o = converNumByReg(o);
        break;
      case STRING:
        o = c.getStringCellValue();
        break;
      default:
        o = "";
        break;
    }
    return o;
  }

  /**
   * 科学数字转换为数字字符串
   */
  public static String numbericToNumberString(String numbericString) {
    String result = matchDoneBigDecimal(numbericString);
    return converNumByReg(result);
  }

  private static String calculationFormula(Cell cell) {

    CellValue cellValue = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator()
        .evaluate(cell);

    return cellValue.formatAsString();
  }

  private static String matchDoneBigDecimal(String bigDecimal) {
    // 对科学计数法进行处理
    boolean flg = Pattern.matches("^-?\\d+(\\.\\d+)?(E-?\\d+)?$", bigDecimal);
    if (flg) {
      BigDecimal bd = new BigDecimal(bigDecimal);
      bigDecimal = bd.toPlainString();
    }
    return bigDecimal;
  }

  private static String converNumByReg(String number) {
    Pattern compile = Pattern.compile("^(\\d+)(\\.0*)?$");
    Matcher matcher = compile.matcher(number);
    while (matcher.find()) {
      number = matcher.group(1);
    }
    return number;
  }

}
